﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;
using System.IO;
using OverStore.Configuration.Components;
using System.Data.SqlServerCe;
using OverStore.Configuration.Builder.Fluent;
using OverStore.Runtime.Storage;
using OverStore.Runtime.ObjectRepository.ErrorTransformer;
using OverStore.Configuration;
using OverStore.Runtime.ObjectRepository;
using OverStore.Runtime.ObjectRepository.Mapping;
using OverStore.Runtime.ObjectRepository.CallbackManager;
using System.Data;

namespace OverStore.TutorialAsTest.Configuration_Examples.MyInventory
{
    #region Entities

    /// <summary>
    /// Base class for entity provides set of properties helps syncronize data between few databases.
    /// </summary>
    public abstract class TrackableEntity
    {
        /// <summary>
        /// Gets or sets the date and time when the entity was created.
        /// If entity is not saved yet, value is <c>default(DateTime)</c>.
        /// </summary>
        public DateTime CreatedAt { get; set; }

        /// <summary>
        /// Gets or sets the date and time when the entity was updated last time.
        /// If entity is just saved, value is equals to CreatedAt value.
        /// If entity is not saved yet, value is <c>default(DateTime)</c>.
        /// </summary>
        public DateTime UpdatedAt { get; set; }

        /// <summary>
        /// Gets or sets the date and time when the entity was deleted.
        /// If entity marked as deleted but not saved yet, value of this property is <c>null</c>.
        /// </summary>
        public DateTime? DeletedAt { get; set; }
    }

    public class Asset : TrackableEntity
    {
        public Guid Id { get; set; }

        public string Name { get; set; }

        public string Description { get; set; }

        public override string ToString()
        {
            return Name ?? base.ToString();
        }
    }

    /// <summary>
    /// Type of actions registered with Document.
    /// </summary>
    [Flags]
    public enum DocumentType
    {
        RegisterAsset = 1,
        DiscardAsset = 2,
        CheckAssetOk = 4,
        CheckAssetDamaged = 8,
        CheckAssetForeign = 16,
        CheckAssetDamagedForeign = 64,
    }

    /// <summary>
    /// Registration of some event in inventory lifecycle.
    /// </summary>
    public class Document : TrackableEntity
    {
        public const int CodeLength = 50;
        public const int CommentLength = 500;

        /// <summary>
        /// Gets primary key of document.
        /// </summary>
        public Guid Id { get; set; }

        /// <summary>
        /// Gets or sets type of event registered by the document.
        /// </summary>
        public DocumentType DocumentType { get; set; }

        /// <summary>
        /// Gets or sets date and time when document created.
        /// </summary>
        public DateTime Date { get; set; }

        /// <summary>
        /// Gets or sets text description of the Document.
        /// </summary>
        public string Comment { get; set; }

        /// <summary>
        /// Gets or sets author of the Document.
        /// </summary>
        public Guid AuthorId { get; set; }

        /// <summary>
        /// Gets or sets asset affected by the event registered with the Document.
        /// </summary>
        public Guid AssetId { get; set; }

        /// <summary>
        /// Gets or sets identification number currnetly assigned to the asset.
        /// </summary>
        public string Code { get; set; }

        /// <summary>
        /// Gets or sets place where event registered with the document occurs.
        /// </summary>
        public Guid PlaceId { get; set; }

        /// <summary>
        /// Gets or sets a value indicating whether code is scanned or entered manually.
        /// </summary>
        public bool IsCodeScanned { get; set; }

        /// <summary>
        /// Returns a <see cref="System.String"/> that represents this instance.
        /// </summary>
        /// <returns>
        /// A <see cref="System.String"/> that represents this instance.
        /// </returns>
        public override string ToString()
        {
            var codeAbbreviation =
                String.Join("",
                            ((IEnumerable<Char>)DocumentType.ToString())
                                .Where(Char.IsUpper)
                                .Select(c => c.ToString())
                                .ToArray());
            return String.Format("{0,-5} №{1} от {2:d}", codeAbbreviation, Code, Date);
        }
    }

    /// <summary>
    /// Read-only entity contains document info.
    /// </summary>
    public class DocumentView
    {
        public Guid Id { get; set; }

        public DocumentType DocumentType { get; set; }

        public string DocumentTypeAbbreviation
        {
            get
            {
                return String.Join("", DocumentType.ToString()
                                    .ToCharArray()
                                    .Where(Char.IsUpper)
                                    .Select(c => c.ToString())
                                    .ToArray());
            }
        }

        public DateTime Date { get; set; }

        public string Comment { get; set; }

        public string Code { get; set; }

        public Guid AuthorId { get; set; }

        public string AuthorName { get; set; }

        public Guid PlaceId { get; set; }

        public string PlaceName { get; set; }

        public Guid AssetId { get; set; }

        public string AssetName { get; set; }

        public bool IsCodeScanned { get; set; }

        /// <summary>
        /// Returns a <see cref="System.String"/> that represents this instance.
        /// </summary>
        /// <returns>
        /// A <see cref="System.String"/> that represents this instance.
        /// </returns>
        public override string ToString()
        {
            return String.Format("{0,-5} №{1} от {2:d}, {3}", DocumentTypeAbbreviation, Code, Date, AssetName);
        }
    }

    /// <summary>
    /// Начало и конец инвентаризации.
    /// </summary>
    public class Inventory : TrackableEntity
    {
        /// <summary>
        /// Идентификатор.
        /// </summary>
        public Guid Id { get; set; }

        /// <summary>
        /// Дата начала инвентаризации.
        /// </summary>
        public DateTime StartDate { get; set; }

        /// <summary>
        /// Дата конца инвентаризации.
        /// </summary>
        public DateTime? EndDate { get; set; }

        /// <summary>
        /// Пользователь, начавший инвентаризацию.
        /// </summary>
        public Guid PersonStarted { get; set; }

        /// <summary>
        /// Пользователь, закончивший инвентаризацию.
        /// </summary>
        public Guid? PersonCompleted { get; set; }

        /// <summary>
        /// Описание инвентаризации.
        /// </summary>
        public string Description { get; set; }

        /// <summary>
        /// Returns a <see cref="System.String"/> that represents this instance.
        /// </summary>
        /// <returns>
        /// A <see cref="System.String"/> that represents this instance.
        /// </returns>
        public override string ToString()
        {
            if (EndDate == null)
                return String.Format("{0} - {1} {2}", StartDate, "Inventory is not completed.", Description);
            else
                return String.Format("{0} - {1} {2}", StartDate, EndDate, Description);
        }
    }

    /// <summary>
    /// User of the system.
    /// </summary>
    public class Person : TrackableEntity
    {
        /// <summary>
        /// Initializes a new instance of the <see cref="Person"/> class.
        /// </summary>
        public Person()
        {
            IsNew = true;
            Role = Role.Employee;
        }

        /// <summary>
        /// Gets or sets the primary key of person.
        /// </summary>
        public Guid Id { get; set; }

        /// <summary>
        /// Gets or sets the login of the user.
        /// </summary>
        public string Login { get; set; }

        /// <summary>
        /// Gets or sets the password of the user.
        /// </summary>
        public string Password { get; set; }

        /// <summary>
        /// Gets or sets the first, last and middle name of the user.
        /// </summary>
        public string Name { get; set; }

        /// <summary>
        /// Gets or sets the role.
        /// </summary>
        public Role Role { get; set; }

        /// <summary>
        /// Service property determines whether entity is new or loaded from database.
        /// </summary>
        public bool IsNew { get; set; }

        /// <summary>
        /// Returns a <see cref="System.String"/> that represents this instance.
        /// </summary>
        /// <returns>
        /// A <see cref="System.String"/> that represents this instance.
        /// </returns>
        public override string ToString()
        {
            if (String.IsNullOrEmpty(Name))
                return Login;
            return Name;
        }
    }

    /// <summary>
    /// User role.
    /// </summary>
    public enum Role
    {
        /// <summary>
        /// Role of default initial user that can only synchronize data and nothing more.
        /// </summary>
        Initial = 1,
        /// <summary>
        /// Employee: can collect asset data, can view and edit assets (due asset data collection), 
        /// can view and edit places (due asset data collection), can synchronize data.
        /// Cannot access admin panel.
        /// </summary>
        Employee = 2,
        /// <summary>
        /// Can perform every function, access admin panel, so he can start and stop inventory,
        /// manage users, and manage place and assets directly.
        /// </summary>
        Administrator = 4,
        /// <summary>
        /// Have all Administrator permissions plus access to developer functionality.
        /// </summary>
        Developer = 8,
    }

    public class Place : TrackableEntity
    {
        public Guid Id { get; set; }

        public string Code { get; set; }

        public string Name { get; set; }

        public Guid? ParentPlaceId { get; set; }

        public override string ToString()
        {
            return Name;
        }
    }

    /// <summary>
    /// Contains information about syncronization try.
    /// </summary>
    public class SyncLog
    {
        public Guid Id { get; set; }

        public Guid UserInitiatorId { get; set; }

        public DateTime StartedAt { get; set; }

        public DateTime? CompletedAt { get; set; }

        public SyncStatus Status { get; set; }
    }

    [Flags]
    public enum SyncStatus
    {
        None = 0,
        Person = 1,
        Inventory = 2,
        Place = 4,
        Asset = 8,
        Document = 16,
        Success = Person | Inventory | Place | Asset | Document
    }

    #endregion

    #region Statistics

    /// <summary>
    /// Provide information about number of assets checked on place.
    /// </summary>
    public class AssetCheckedOnPlace
    {
        /// <summary>
        /// Gets or sets the asset checked due last inventory.
        /// </summary>
        /// <value></value>
        public int AssetChecked { get; set; }

        /// <summary>
        /// Gets or sets the total number of assets registered on place.
        /// </summary>
        public int AssetTotal { get; set; }
    }

    public class InventoryInfo
    {
        public InventoryInfo(Inventory currentRunningInventory, Inventory lastCompletedInventory)
        {
            CurrentRunningInventory = currentRunningInventory;
            LastCompletedInventory = lastCompletedInventory;
        }

        public Inventory CurrentRunningInventory { get; private set; }

        public Inventory LastCompletedInventory { get; private set; }
    }

    #endregion


    class Configuration
    {
        #region Reader Parameters

        /// <summary>
        /// Parameters for searching for entities.
        /// </summary>
        public class SearchParam
        {
            private string _Pattern;
            public string Pattern
            {
                get
                {
                    return _Pattern;
                }
                set
                {
                    _Pattern = (value ?? String.Empty).Trim('%') + '%';
                }
            }
        }

        private class LoginParam
        {
            public string Login { get; set; }

            public string Password { get; set; }
        }

        private class ScanPlaceParam
        {
            public string Code { get; set; }
        }

        private class ScanDocumentParam
        {
            public string Code { get; set; }
        }

        private class PreviousInventoryParam
        {
            private DateTime _StartDate;

            public DateTime StartDate
            {
                get
                {
                    return _StartDate.ToUniversalTime();
                }
                set
                {
                    _StartDate = value.ToUniversalTime();
                }
            }
        }

        private class LastClosedInventoryStartStopParam
        {
        }

        private class LastInventoryParam { }

        private class InventoryInfoParam { }

        private class AssetRegistryParam
        {
            public Guid PlaceId { get; set; }
        }

        private class DocumentsOnPlaceParam
        {
            public Guid PlaceId { get; set; }
        }

        public class DocumentViewInDateRangeParameter
        {
            private DateTime? _StartDate;
            private DateTime? _EndDate;

            public DateTime? StartDate
            {
                get
                {
                    return _StartDate.ToUniversalTime();
                }
                set
                {
                    _StartDate = value.ToUniversalTime();
                }
            }

            public DateTime? EndDate
            {
                get
                {
                    return _EndDate.ToUniversalTime();
                }
                set
                {
                    _EndDate = value.ToUniversalTime();
                }
            }
        }

        /// <summary>
        /// Parameters for obtain records since last synchronization.
        /// Each entity must provide parameterized readers with this parameter type
        /// in order to support changes reading.
        /// </summary>
        class SyncParam
        {
            private DateTime? _LastSyncDate;
            private DateTime _CurrentSyncDate;

            public DateTime? LastSyncDate
            {
                get
                {
                    return _LastSyncDate.ToUniversalTime();
                }
                set
                {
                    _LastSyncDate = value.ToUniversalTime();
                }
            }

            /// <summary>
            /// Gets or sets the date of the current synchronization.
            /// It is used for avoid putting items in changeset which was 
            /// updated due current synchronization.
            /// </summary>
            public DateTime CurrentSyncStartDate
            {
                get
                {
                    return _CurrentSyncDate.ToUniversalTime();
                }
                set
                {
                    _CurrentSyncDate = value.ToUniversalTime();
                }
            }
        }

        class LatestSuccessfulSyncLogParam
        {
            public SyncStatus SuccessfulStatus
            {
                get
                {
                    return SyncStatus.Success;
                }
            }
        }

        class LatestUnfinishedSyncLogParam
        {
            public SyncStatus FinishedStatus
            {
                get
                {
                    return SyncStatus.Success;
                }
            }
        }

        class SelectSinglePerson
        {
            public Guid Id { get; set; }
        }

        class AssetCheckedOnPlaceParam
        {
            public Guid PlaceId { get; set; }

            public DateTime LastSyncStartDate { get; set; }
        }

        #endregion

        #region Connection String

        /// <summary>
        /// Gets the default connection string.
        /// </summary>
        /// <value>The default connection string.</value>
        public static string LocalSqlCeConnectionString
        {
            get
            {
                return BuildLocalSqlCeConnectionString("Inventory.sdf");
            }
        }

        /// <summary>
        /// Builds the connection string for local SQL CE database.
        /// </summary>
        public static string BuildLocalSqlCeConnectionString(string databaseFileShortName)
        {
            var directory = Assembly.GetExecutingAssembly().GetName().CodeBase.Replace("file:///", "");
            var dbFileName = Path.Combine(Path.GetDirectoryName(directory), databaseFileShortName);
            return String.Format("Data Source = '{0}'", dbFileName);
        }

        #endregion

        private static IComponentConfiguration<IRepository<DocumentView>> CreateDocViewRepository()
        {
            return Component.MapClassToTable<DocumentView, Guid>("DocumentView")
                            .MapKey("Id", e => e.Id, (e, v) => e.Id = v,
                                    Component.IdentifierGenerator.Guid<DocumentView>())
                            .MapScalarValue("AuthorId", e => e.AuthorId, (e, v) => e.AuthorId = v)
                            .MapScalarValue<string>("AuthorName", e => e.AuthorName, (e, v) => e.AuthorName = v)
                            .MapScalarValue<string>("Code", e => e.Code, (e, v) => e.Code = v)
                            .MapNullableScalarValue<string>("Comment", e => e.Comment, (e, v) => e.Comment = v)
                            .MapScalarValue<DateTime>("DocDate", e => e.Date.ToUniversalTime(), (e, v) => e.Date = v.AsUtc())
                            .MapScalarValue<int>("DocType", e => (int)e.DocumentType, (e, v) => e.DocumentType = (DocumentType)v)
                            .MapScalarValue<Guid>("AssetId", e => e.AssetId, (e, v) => e.AssetId = v)
                            .MapScalarValue<string>("AssetName", e => e.AssetName, (e, v) => e.AssetName = v)
                            .MapScalarValue<Guid>("PlaceId", e => e.PlaceId, (e, v) => e.PlaceId = v)
                            .MapScalarValue<String>("PlaceName", e => e.PlaceName, (e, v) => e.PlaceName = v);
        }

        private static IComponentConfiguration<IMapper<DocumentView>> CreateDocViewMapper()
        {
            return Component.Mapper.Delegate<DocumentView>(
                (entity, name, provider) =>
                {
                    if (name == "orig__Id")
                        return provider.GetIdentifier<DocumentView, Guid>(entity);
                    return Missing.Value;
                },
                (data, entity, provider) =>
                {
                    entity.AuthorId = data.GetGuid("AuthorId");
                    entity.AuthorName = data.GetString("AuthorName");
                    entity.Code = data.GetString("Code");
                    entity.Comment = data.GetString("Comment");
                    entity.Date = data.GetDateTime("DocDate").AsUtc();
                    entity.DocumentType = (DocumentType)data.GetInt32("DocType");
                    entity.AssetId = data.GetGuid("AssetId");
                    entity.AssetName = data.GetString("AssetName");
                    entity.PlaceId = data.GetGuid("PlaceId");
                    entity.PlaceName = data.GetString("PlaceName");
                    entity.IsCodeScanned = data.GetBoolean("IsCodeScanned");
                });
        }

        /// <summary>
        /// Sets up properties of <see cref="TrackableEntity"/> to correct values on persisting operations.
        /// </summary>
        private static bool TrackableEntityCallback<TEntity>(TEntity entity, PersistenceOperation operation)
            where TEntity : TrackableEntity
        {
            if (operation == PersistenceOperation.Inserting)
            {
                var now = DateTime.Now.ToUniversalTime();
                entity.CreatedAt = now;
                entity.UpdatedAt = now;
                return true;
            }
            if (operation == PersistenceOperation.Updating)
            {
                entity.UpdatedAt = DateTime.Now.ToUniversalTime();
                return true;
            }
            if (operation == PersistenceOperation.Deleting)
            {
                entity.DeletedAt = DateTime.Now.ToUniversalTime();
                return true;
            }
            return true;
        }

       private static bool TrackableEntityIsNewIf<TEntity>(TEntity entity)
            where TEntity : TrackableEntity
        {
            return false;
        }

        public static IPersistenceSessionFactory CreateConfiguration(string connectionString)
        {
            if (String.IsNullOrEmpty(connectionString))
                throw new ArgumentNullException("connectionString");

            // Note all dates must be stored as UTC dates.

            return OverStoreFluent.Configure()
                .LogToDebug()
                .ConnectToDatabase<SqlCeConnection>(connectionString, ProcessOpenedReadersMode.ForciblyCloseReaders)
                //.ConnectToDatabase<System.Data.SqlClient.SqlConnection>("Data Source=localhost\\SQLEXPRESS2008;Initial Catalog=Inventory;Integrated Security=True;MultipleActiveResultSets=True", ProcessOpenedReadersMode.ForciblyCloseReaders)
                .MapClassToTable<Place, Guid>("Place")
                    .MapKey("PlaceId", e => e.Id, (e, v) => e.Id = v, Component.IdentifierGenerator.Guid<Place>())
                    .MapScalarValue("IdentificationNumber", e => e.Code, (e, v) => e.Code = v.TrimToMaxLen(100))
                    .MapScalarValue("Name", e => e.Name, (e, v) => e.Name = v.TrimToMaxLen(150))
                    .MapNullableScalarValue("ParentPlaceId", e => e.ParentPlaceId, (e, v) => e.ParentPlaceId = v)
                    .MapScalarValue<DateTime>("CreatedAt", e => e.CreatedAt.ToUniversalTime(), (e, v) => e.CreatedAt = v.AsUtc())
                    .MapScalarValue<DateTime>("UpdatedAt", e => e.UpdatedAt.ToUniversalTime(), (e, v) => e.UpdatedAt = v.AsUtc())
                    .MapNullableScalarValue<DateTime?>("DeletedAt", e => e.DeletedAt.ToUniversalTime(), (e, v) => e.DeletedAt = v.AsUtc())
                    .AddCallback(TrackableEntityCallback<Place>)
                    .TransformError<SqlCeException>("UK_Place_Name", MessageMatchType.MessageContainsPattern, "Place Name is not unique.")
                    .TransformError<SqlCeException>("UK_Place_IdentificationNumber", MessageMatchType.MessageContainsPattern, "Place code is not unique.")
                    .ConfigureAttacher()
                        .ExtractIdentifier(e => e.Id)
                        .NeverGenerateNewIdentifier()
                        .InstanceIsNewIf(TrackableEntityIsNewIf<Place>)
                        .ForceDataChanged()
                    .End()
                    .ParameterlessReader(true)
                        .ReadCommand()
                            .UseSqlCommand("select * from Place")
                        .EndCommand()
                        .DelegateIdentifierProvider(r => r.GetGuid("PlaceId"))
                    .EndReader()
                    .ParameterizedReader<SearchParam>(true)
                        .ReadCommand()
                            .UseSqlCommand("select * from Place where name like @Pattern Or identificationNumber like @Pattern")
                            .InputParameter("Pattern", System.Data.DbType.String)
                        .EndCommand()
                        .MapParametersUsingReflection()
                        .DelegateIdentifierProvider(r => r.GetGuid("PlaceId"))
                    .EndReader()
                    .ParameterizedReader<ScanPlaceParam>(true)
                        .ReadCommand()
                            .UseSqlCommand("select * from Place where IdentificationNumber = @code")
                            .InputParameter("code", System.Data.DbType.String)
                        .EndCommand()
                        .MapParametersUsingReflection()
                        .DelegateIdentifierProvider(d => d.GetGuid("PlaceId"))
                   .EndReader()
                   .ParameterizedReader<SyncParam>(true)
                        .ReadCommand()
                            .UseSqlCommand("select * from Place where UpdatedAt < @CurrentSyncStartDate and (UpdatedAt >= @LastSyncDate or @lastSyncDate is null)")
                            .NullableInputParameter("LastSyncDate", DbType.DateTime)
                            .InputParameter("CurrentSyncStartDate", DbType.DateTime)
                        .EndCommand()
                        .MapParametersUsingReflection()
                        .DelegateIdentifierProvider(d => d.GetGuid("PlaceId"))
                    .EndReader()
                .EndTableMapping()
                .MapClassToTable<Person, Guid>("Person")
                    .MapKey("PersonId", p => p.Id, (p, id) => p.Id = id, Component.IdentifierGenerator.Guid<Person>())
                    .MapScalarValue("Login", p => p.Login, (e, v) => e.Login = v.TrimToMaxLen(50))
                    .MapScalarValue("Password", e => e.Password, (e, v) => e.Password = v.TrimToMaxLen(50))
                    .MapScalarValue("Name", e => e.Name, (e, v) => e.Name = v.TrimToMaxLen(150))
                    .MapScalarValue("Role", e => (int)e.Role, (e, v) => e.Role = (Role)v)
                    .MapScalarValue<DateTime>("CreatedAt", e => e.CreatedAt.ToUniversalTime(), (e, v) => e.CreatedAt = v.AsUtc())
                    .MapScalarValue<DateTime>("UpdatedAt", e => e.UpdatedAt.ToUniversalTime(), (e, v) => e.UpdatedAt = v.AsUtc())
                    .MapNullableScalarValue<DateTime?>("DeletedAt", e => e.DeletedAt.ToUniversalTime(), (e, v) => e.DeletedAt = v.AsUtc())
                    .AddCallback((e, o) =>
                    {
                        if (o == PersistenceOperation.Loading)
                            e.IsNew = false;
                        return TrackableEntityCallback<Person>(e, o);
                    })
                    .TransformError<SqlCeException>("UK_Person_Login", MessageMatchType.MessageContainsPattern, "Login is not unique")
                    .ConfigureAttacher()
                        .ExtractIdentifier(e => e.Id)
                        .NeverGenerateNewIdentifier()
                        .InstanceIsNewIf(TrackableEntityIsNewIf<Person>)
                        .ForceDataChanged()
                    .End()
                    .ParameterlessReader(true)
                        .ReadCommand()
                            .UseSqlCommand("select * from Person")
                        .EndCommand()
                        .DelegateIdentifierProvider(r => r.GetGuid("PersonId"))
                    .EndReader()
                    .ParameterizedReader<SearchParam>(true)
                        .ReadCommand()
                            .UseSqlCommand("select * from Person where Login like @Pattern or Name like @Pattern")
                            .InputParameter("Pattern", DbType.String)
                        .EndCommand()
                        .MapParametersUsingReflection()
                        .DelegateIdentifierProvider(r => r.GetGuid("PersonId"))
                    .EndReader()
                    .ParameterizedReader<SyncParam>(true)
                        .ReadCommand()
                            .UseSqlCommand("select * from Person where UpdatedAt < @CurrentSyncStartDate and (UpdatedAt >= @LastSyncDate or @lastSyncDate is null)")
                            .NullableInputParameter("LastSyncDate", DbType.DateTime)
                            .InputParameter("CurrentSyncStartDate", DbType.DateTime)
                        .EndCommand()
                        .MapParametersUsingReflection()
                        .DelegateIdentifierProvider(d => d.GetGuid("PersonId"))
                    .EndReader()
                    .ParameterizedReader<SelectSinglePerson>(true)
                        .ReadCommand()
                            .UseSqlCommand("Select * from Person where PersonId = @Id")
                            .InputParameter("Id", DbType.Guid)
                        .EndCommand()
                        .MapParametersUsingReflection()
                        .DelegateIdentifierProvider(r => r.GetGuid("PersonId"))
                    .EndReader()
                    .ParameterizedReader<LoginParam>(true)
                        .ReadCommand()
                            .UseSqlCommand("select * from Person where Login = @Login and Password = @Password")
                            .InputParameter("Login", DbType.String)
                            .InputParameter("Password", DbType.String)
                        .EndCommand()
                        .MapParametersUsingReflection()
                        .DelegateIdentifierProvider(r => r.GetGuid("PersonId"))
                    .EndReader()
                .EndTableMapping()
                .MapClassToTable<Asset, Guid>("Asset")
                    .MapKey("AssetId", e => e.Id, (e, id) => e.Id = id, Component.IdentifierGenerator.Guid<Asset>())
                    .MapScalarValue("Name", e => e.Name, (e, v) => e.Name = v.TrimToMaxLen(300), "Asset name is required.")
                    .MapNullableScalarValue("Description", e => e.Description, (e, v) => e.Description = v.TrimToMaxLen(500))
                    .MapScalarValue<DateTime>("CreatedAt", e => e.CreatedAt.ToUniversalTime(), (e, v) => e.CreatedAt = v.AsUtc())
                    .MapScalarValue<DateTime>("UpdatedAt", e => e.UpdatedAt.ToUniversalTime(), (e, v) => e.UpdatedAt = v.AsUtc())
                    .MapNullableScalarValue<DateTime?>("DeletedAt", e => e.DeletedAt.ToUniversalTime(), (e, v) => e.DeletedAt = v.AsUtc())
                    .AddCallback(TrackableEntityCallback<Asset>)
                    .TransformError<SqlCeException>("UK_Asset_Name", MessageMatchType.MessageContainsPattern, "Asset name is not unique.")
                    .ConfigureAttacher()
                        .ExtractIdentifier(e => e.Id)
                        .NeverGenerateNewIdentifier()
                        .InstanceIsNewIf(TrackableEntityIsNewIf<Asset>)
                        .ForceDataChanged()
                    .End()
                    .ParameterlessReader(true)
                        .ReadCommand()
                            .UseSqlCommand("select * from Asset")
                        .EndCommand()
                        .DelegateIdentifierProvider(r => r.GetGuid("AssetId"))
                    .EndReader()
                    .ParameterizedReader<SearchParam>(true)
                        .ReadCommand()
                            .UseSqlCommand("select * from Asset where name like @pattern")
                            .InputParameter("pattern", System.Data.DbType.String)
                        .EndCommand()
                        .MapParametersUsingReflection()
                        .DelegateIdentifierProvider(r => r.GetGuid("AssetId"))
                    .EndReader()
                    .ParameterizedReader<SyncParam>(true)
                        .ReadCommand()
                            .UseSqlCommand("select * from Asset where UpdatedAt < @CurrentSyncStartDate and (UpdatedAt >= @LastSyncDate or @lastSyncDate is null)")
                            .NullableInputParameter("LastSyncDate", DbType.DateTime)
                            .InputParameter("CurrentSyncStartDate", DbType.DateTime)
                        .EndCommand()
                        .MapParametersUsingReflection()
                        .DelegateIdentifierProvider(d => d.GetGuid("AssetId"))
                    .EndReader()
                .EndTableMapping()
                .MapClassToTable<Document, Guid>("Document")
                    .MapKey("DocumentId", e => e.Id, (e, id) => e.Id = id, Component.IdentifierGenerator.Guid<Document>())
                    .MapScalarValue("DocType", e => (int)e.DocumentType, (e, v) => e.DocumentType = (DocumentType)v)
                    .MapScalarValue("DocDate", e => e.Date.ToUniversalTime(), (e, v) => e.Date = v.AsUtc())
                    .MapNullableScalarValue("Comment", e => e.Comment, (e, v) => e.Comment = v.TrimToMaxLen(Document.CommentLength))
                    .MapScalarValue("AuthorId", e => e.AuthorId, (e, v) => e.AuthorId = v)
                    .MapScalarValue("AssetId", e => e.AssetId, (e, v) => e.AssetId = v)
                    .MapScalarValue("Code", e => e.Code, (e, v) => e.Code = v.TrimToMaxLen(Document.CodeLength))
                    .MapScalarValue("PlaceId", e => e.PlaceId, (e, v) => e.PlaceId = v)
                    .MapScalarValue("IsCodeScanned", e => e.IsCodeScanned, (e, v) => e.IsCodeScanned = v)
                    .MapScalarValue<DateTime>("CreatedAt", e => e.CreatedAt.ToUniversalTime(), (e, v) => e.CreatedAt = v.AsUtc())
                    .MapScalarValue<DateTime>("UpdatedAt", e => e.UpdatedAt.ToUniversalTime(), (e, v) => e.UpdatedAt = v.AsUtc())
                    .MapNullableScalarValue<DateTime?>("DeletedAt", e => e.DeletedAt.ToUniversalTime(), (e, v) => e.DeletedAt = v.AsUtc())
                    .AddCallback(TrackableEntityCallback<Document>)
                    .ConfigureAttacher()
                        .ExtractIdentifier(e => e.Id)
                        .NeverGenerateNewIdentifier()
                        .InstanceIsNewIf(TrackableEntityIsNewIf<Document>)
                        .ForceDataChanged()
                    .End()
                    .ParameterlessReader(true)
                        .ReadCommand()
                            .UseSqlCommand("select * from Document")
                        .EndCommand()
                        .DelegateIdentifierProvider(r => r.GetGuid("DocumentId"))
                    .EndReader()
                    .ParameterizedReader<ScanDocumentParam>(true)
                        .ReadCommand()
                            .UseSqlCommand(SqlStrings.ScanDocument)
                            .InputParameter("code", System.Data.DbType.String)
                        .EndCommand()
                        .MapParametersUsingReflection()
                        .DelegateIdentifierProvider(r => r.GetGuid("DocumentId"))
                    .EndReader()
                    .ParameterizedReader<DocumentsOnPlaceParam>(true)
                        .ReadCommand()
                            .UseSqlCommand(SqlStrings.DocumentsOnPlace)
                            .InputParameter("placeid", DbType.Guid)
                        .EndCommand()
                        .MapParametersUsingReflection()
                        .DelegateIdentifierProvider(r => r.GetGuid("DocumentId"))
                    .EndReader()
                    .ParameterizedReader<SyncParam>(true)
                        .ReadCommand()
                            .UseSqlCommand("select * from Document where UpdatedAt < @CurrentSyncStartDate and (UpdatedAt >= @LastSyncDate or @lastSyncDate is null)")
                            .NullableInputParameter("LastSyncDate", DbType.DateTime)
                            .InputParameter("CurrentSyncStartDate", DbType.DateTime)
                        .EndCommand()
                        .MapParametersUsingReflection()
                        .DelegateIdentifierProvider(d => d.GetGuid("DocumentId"))
                    .EndReader()
                .EndTableMapping()
                .CustomClassRepository<DocumentView, Guid>()
                    .DelegateDepot()
                        .CreateInstance(() => new DocumentView())
                        .StoreIdentifier((e, id) => e.Id = id)
                        .Guid()
                    .EndDepot()
                    .WithMapper(CreateDocViewMapper())
                    .CommandSet()
                        .SelectCommand()
                            .UseSqlCommand(SqlStrings.DocumentViewSelectSingle)
                            .InputParameter("orig__Id", DbType.Guid)
                        .EndCommand()
                        .AsReadOnly()
                    .EndCommandSet()
                    .ByIdentifierAttaching(e => e.Id)
                    .ParameterlessReader(true)
                        .ReadCommand()
                            .UseSqlCommand(SqlStrings.DocumentViewSelectAll)
                        .EndCommand()
                        .DelegateIdentifierProvider(d => d.GetGuid("Id"))
                    .EndReader()
                    .ParameterizedReader<AssetRegistryParam>(true)
                        .ReadCommand()
                            .UseSqlCommand(SqlStrings.AssetRegistryOnPlace)
                            .InputParameter("PlaceId", DbType.Guid)
                        .EndCommand()
                        .MapParametersUsingReflection()
                        .DelegateIdentifierProvider(r => r.GetGuid("Id"))
                    .EndReader()
                    .ParameterizedReader<DocumentViewInDateRangeParameter>(true)
                        .ReadCommand()
                            .UseSqlCommand(SqlStrings.DocumentsInDateRange)
                            .NullableInputParameter("StartDate", DbType.DateTime)
                            .NullableInputParameter("EndDate", DbType.DateTime)
                        .EndCommand()
                        .MapParametersUsingReflection()
                        .DelegateIdentifierProvider(r => r.GetGuid("Id"))
                    .EndReader()
                    .ParameterizedReader<SearchParam>(true)
                        .ReadCommand()
                            .UseSqlCommand(SqlStrings.DocumentsSearch)
                            .InputParameter("Pattern", DbType.String)
                        .EndCommand()
                        .MapParametersUsingReflection()
                        .DelegateIdentifierProvider(r => r.GetGuid("Id"))
                    .EndReader()
                .EndObjectRepository()
                .MapClassToTable<Inventory, Guid>("Inventory")
                    .MapKey("Id", e => e.Id, (e, id) => e.Id = id,
                        Component.IdentifierGenerator.Guid<Inventory>())
                    .MapScalarValue("StartDate", e => e.StartDate.ToUniversalTime(), (e, v) => e.StartDate = v.AsUtc())
                    .MapNullableScalarValue("EndDate", e => e.EndDate.ToUniversalTime(), (e, v) => e.EndDate = v.AsUtc())
                    .MapScalarValue("PersonStarted", e => e.PersonStarted, (e, v) => e.PersonStarted = v)
                    .MapNullableScalarValue("PersonCompleted", e => e.PersonCompleted, (e, v) => e.PersonCompleted = v)
                    .MapNullableScalarValue("Description", e => e.Description, (e, v) => e.Description = v.TrimToMaxLen(500))
                    .MapScalarValue<DateTime>("CreatedAt", e => e.CreatedAt.ToUniversalTime(), (e, v) => e.CreatedAt = v.AsUtc())
                    .MapScalarValue<DateTime>("UpdatedAt", e => e.UpdatedAt.ToUniversalTime(), (e, v) => e.UpdatedAt = v.AsUtc())
                    .MapNullableScalarValue<DateTime?>("DeletedAt", e => e.DeletedAt.ToUniversalTime(), (e, v) => e.DeletedAt = v.AsUtc())
                    .AddCallback(TrackableEntityCallback<Inventory>)
                    .ConfigureAttacher()
                        .ExtractIdentifier(e => e.Id)
                        .NeverGenerateNewIdentifier()
                        .InstanceIsNewIf(TrackableEntityIsNewIf<Inventory>)
                        .ForceDataChanged()
                    .End()
                    .ParameterlessReader(true)
                        .ReadCommand()
                            .UseSqlCommand("select * from Inventory order by StartDate desc")
                        .EndCommand()
                        .DelegateIdentifierProvider(d => d.GetGuid("Id"))
                    .EndReader()
                    .ParameterizedReader<SearchParam>(true)
                        .ReadCommand()
                            .UseSqlCommand(SqlStrings.InventorySearch)
                            .InputParameter("Pattern", DbType.String)
                        .EndCommand()
                        .MapParametersUsingReflection()
                        .DelegateIdentifierProvider(r => r.GetGuid("Id"))
                    .EndReader()
                    .ParameterizedReader<PreviousInventoryParam>(true)
                        .ReadCommand()
                            .UseSqlCommand(SqlStrings.PreviousInventarization)
                            .InputParameter("StartDate", DbType.DateTime)
                        .EndCommand()
                        .MapParametersUsingReflection()
                        .DelegateIdentifierProvider(r => r.GetGuid("Id"))
                    .EndReader()
                    .ParameterizedReader<LastClosedInventoryStartStopParam>(true)
                        .ReadCommand()
                            .UseSqlCommand("select Top (1) * from Inventory where endDate is not null order by EndDate desc")
                        .EndCommand()
                        .MapParametersUsingReflection()
                        .DelegateIdentifierProvider(r => r.GetGuid("Id"))
                    .EndReader()
                    .ParameterizedReader<LastInventoryParam>(true)
                        .ReadCommand()
                            .UseSqlCommand("select top (1) * from Inventory order by startdate desc")
                        .EndCommand()
                        .MapParametersUsingReflection()
                        .DelegateIdentifierProvider(r => r.GetGuid("Id"))
                    .EndReader()
                    .ParameterizedReader<InventoryInfoParam>(true)
                        .ReadCommand()
                            .UseSqlCommand(SqlStrings.InventoryInfo)
                        .EndCommand()
                        .MapParametersUsingReflection()
                        .DelegateIdentifierProvider(r => r.GetGuid("Id"))
                    .EndReader()
                    .ParameterizedReader<SyncParam>(true)
                        .ReadCommand()
                            .UseSqlCommand("select * from Inventory where UpdatedAt < @CurrentSyncStartDate and (UpdatedAt >= @LastSyncDate or @lastSyncDate is null)")
                            .NullableInputParameter("LastSyncDate", DbType.DateTime)
                            .InputParameter("CurrentSyncStartDate", DbType.DateTime)
                        .EndCommand()
                        .MapParametersUsingReflection()
                        .DelegateIdentifierProvider(d => d.GetGuid("Id"))
                    .EndReader()
                .EndTableMapping()
                .MapClassToTable<SyncLog, Guid>("SyncLog")
                    .MapKey("Id", e => e.Id, (e, v) => e.Id = v, Component.IdentifierGenerator.Guid<SyncLog>())
                    .MapScalarValue<Guid>("UserInitiatorId", e => e.UserInitiatorId, (e, v) => e.UserInitiatorId = v)
                    .MapScalarValue<DateTime>("StartedAt", e => e.StartedAt.ToUniversalTime(), (e, v) => e.StartedAt = v.AsUtc())
                    .MapNullableScalarValue<DateTime?>("CompletedAt", e => e.CompletedAt.ToUniversalTime(), (e, v) => e.CompletedAt = v.AsUtc())
                    .MapScalarValue<int>("Status", e => (int)e.Status, (e, v) => e.Status = (SyncStatus)v)
                    .ParameterlessReader(true)
                        .ReadCommand()
                            .UseSqlCommand("select top (10) * from SyncLog order by CompletedAt desc")
                        .EndCommand()
                        .DelegateIdentifierProvider(r => r.GetGuid("Id"))
                    .EndReader()
                    .ParameterizedReader<LatestSuccessfulSyncLogParam>(true)
                        .ReadCommand()
                            .UseSqlCommand("select top (1) * from SyncLog where CompletedAt is not null and status = @Status order by CompletedAt desc")
                            .InputParameter("Status", DbType.Int32)
                        .EndCommand()
                        .DelegateMapper((entity, name, provider) =>
                        {
                            if (name == "Status")
                                return (int)entity.SuccessfulStatus;
                            return Missing.Value;
                        })
                        .DelegateIdentifierProvider(r => r.GetGuid("Id"))
                    .EndReader()
                    .ParameterizedReader<LatestUnfinishedSyncLogParam>(true)
                        .ReadCommand()
                            .UseSqlCommand("select top (1) * from SyncLog where CompletedAt is not null and status <> @Status order by CompletedAt desc")
                            .InputParameter("Status", DbType.Int32)
                        .EndCommand()
                        .DelegateMapper((entity, name, provider) =>
                        {
                            if (name == "Status")
                                return (int)entity.FinishedStatus;
                            return Missing.Value;
                        })
                        .DelegateIdentifierProvider(r => r.GetGuid("Id"))
                    .EndReader()
                .EndTableMapping()
                .MapClassToTable<AssetCheckedOnPlace, Guid>("Fake")
                    .MapKey("Id", e => Guid.NewGuid(), (e, v) => { }, OverStore.Configuration.Components.Component.IdentifierGenerator.Guid<AssetCheckedOnPlace>())
                    .MapScalarValue("AssetChecked", e => e.AssetChecked, (e, v) => e.AssetChecked = v)
                    .MapScalarValue("AssetTotal", e => e.AssetTotal, (e, v) => e.AssetTotal = v)
                    .ParameterizedReader<AssetCheckedOnPlaceParam>(true)
                        .ReadCommand()
                            .UseSqlCommand(SqlStrings.AssetCheckedOnPlace)
                            .InputParameter("LastSyncStartDate", DbType.DateTime)
                            .InputParameter("PlaceId", DbType.Guid)
                        .EndCommand()
                        .MapParametersUsingReflection()
                        .DelegateIdentifierProvider(r => Guid.NewGuid())
                    .EndReader()
                .EndTableMapping()
            .End();
        }

    }

    #region Utils

    public static class Utils
    {
        /// <summary>
        /// Creates new DateTime object with the same values as argument but with Utc kind.
        /// </summary>
        public static DateTime AsUtc(this DateTime value)
        {
            return DateTime.SpecifyKind(value, DateTimeKind.Utc);
        }

        /// <summary>
        /// If value is null, returns null, otherwise creates new DateTime object with the same values as argument but with Utc kind.
        /// </summary>
        public static DateTime? AsUtc(this DateTime? value)
        {
            if (!value.HasValue)
                return null;
            return value.Value.AsUtc();
        }

        /// <summary>
        /// Converts the value of the current System.DateTime object to Coordinated Universal Time (UTC).
        /// </summary>
        public static DateTime? ToUniversalTime(this DateTime? value)
        {
            if (!value.HasValue)
                return null;
            return value.Value.ToUniversalTime();
        }

        /// <summary>
        /// If string length greater that specified length, trim string end to max length.
        /// </summary>
        public static string TrimToMaxLen(this string value, int maxLength)
        {
            if (String.IsNullOrEmpty(value))
                return value;
            if (value.Length > maxLength)
                return value.Substring(0, maxLength);
            else
                return value;
        }
    }

    #endregion
}
